﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Time_Entry table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectTimeEntryById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectTimeEntryById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectTimeEntryById]
    @User_Id int,
	@Requirement_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Time_Entry] WHERE User_Id = @User_ID AND Requirement_Id = @Requirement_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertTimeEntry]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertTimeEntry]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertTimeEntry]
	@User_Id int,
	@Requirement_Id int,
	@Time_Worked decimal(4,1)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Time_Entry] 
	(
	User_Id,
	Requirement_Id,
	Time_Worked
	)
VALUES 
	(
	@User_Id,
	@Requirement_Id,
	@Time_Worked
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateTimeEntry]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateTimeEntry]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateTimeEntry]
    @User_Id int,
	@Requirement_Id int,
	@Time_Worked decimal (4,1)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Time_Entry] 
SET 
	Time_Worked = @Time_Worked
WHERE User_Id = @User_Id AND Requirement_Id = @Requirement_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteTimeEntry]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteTimeEntry]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteTimeEntry]
    @User_Id int,
	@Requirement_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Time_Entry] 
WHERE User_Id = @User_Id AND Requirement_Id = @Requirement_Id

END
'
GO 